<cfcomponent displayname="KronosQueries" output="false">

    <cffunction access="public" name="getCurrentEmployees" returntype="query">
        <cfargument default="-1" name="HR_PersonIDNo" required="false" />
        <cfquery name="this_query" datasource="#SESSION.DataSourceWFCDB#">
            SELECT *
            FROM CURRENT_EMPLOYEES
            <cfif arguments.HR_PersonIDNo NEQ -1>
                WHERE PersonIDNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.HR_PersonIDNo#" />
            </cfif>
        </cfquery>
        <cfreturn this_query />
    </cffunction>
    
    
    <cffunction access="public" name="getEmployeeAddress" returntype="query">
        <cfargument default="-1" name="PersonIDNo" required="false" />
        <cfquery name="this_query" datasource="#SESSION.DataSourceWFCDB#">
            SELECT tPERSON_ADDRESSES.PersonIdNo, tPERSON_ADDRESSES.PersonAddressTypeIdNo, tPERSON_ADDRESSES.PersonAddressPrimaryInd, 
            tPERSON_ADDRESSES.PersonAddress1, tPERSON_ADDRESSES.PersonAddress2, tPERSON_ADDRESSES.PersonAddressCity, 
            tPERSON_ADDRESSES.PersonAddressStateProvinceIdNo, tPERSON_ADDRESSES.PersonAddressPostalCode, 
            tPERSON_ADDRESSES.PersonAddressFromEffectDate, tLOCAL_CODES.LocalCode, tLOCAL_CODES.LocalCodeAbbreviation AS State, tLOCAL_CODES.LocalCodeType
            FROM tPERSON_ADDRESSES LEFT OUTER JOIN
            tLOCAL_CODES ON tPERSON_ADDRESSES.PersonAddressStateProvinceIdNo = tLOCAL_CODES.LocalCodeIdNo
            <cfif arguments.PersonIDNo NEQ -1>
                WHERE PersonIDNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.PersonIDNo#" /> AND
                (GETDATE() BETWEEN PersonAddressFromEffectDate AND PersonAddressToEffectDate)
            </cfif>
        </cfquery>
        <cfreturn this_query />
    </cffunction>
    
    
    
    <cffunction access="public" name="getEmployeePhones" returntype="query">
        <cfargument default="-1" name="PersonIDNo" required="false" />
        <cfquery name="this_query" datasource="#SESSION.DataSourceWFCDB#">
            SELECT tPERSON_PHONES.PersonPhoneTypeIdNo,tPERSON_PHONES.PersonPhonePrimaryInd, tPERSON_PHONES.PersonPhoneNo, tPERSON_PHONES.PersonPhoneExt,tTYPE_CODES.TypeCode
            FROM tPERSON_PHONES INNER JOIN
            tTYPE_CODES ON tPERSON_PHONES.PersonPhoneTypeIdNo = tTYPE_CODES.TypeCodeIdNo
            <cfif arguments.PersonIDNo NEQ -1>
                WHERE PersonIDNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.PersonIDNo#" /> AND
                (GETDATE() BETWEEN PersonPhoneFromEffectDate AND PersonPhoneToEffectDate)
            </cfif>
            ORDER BY tTYPE_CODES.TypeCode
        </cfquery>
        <cfreturn this_query />
    </cffunction>
    
    
    <cffunction access="public" name="getEmployeeEmail" returntype="query">
        <cfargument default="-1" name="PersonIDNo" required="false" />
        <cfquery name="this_query" datasource="#SESSION.DataSourceWFCDB#">
            SELECT EMailAddress, EMailPrimaryInd
            FROM tEMAIL
            <cfif arguments.PersonIDNo NEQ -1>
                WHERE PersonIDNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.PersonIDNo#" /> AND
                (GETDATE() BETWEEN EMailFromEffectDate AND EMailToEffectDate)
            </cfif>
            ORDER BY EMailPrimaryInd DESC
        </cfquery>
        <cfreturn this_query />
    </cffunction>
    
    
    
    <cffunction access="public" name="getEmployeeContacts" returntype="query">
        <cfargument default="-1" name="PersonIDNo" required="false" />
        <cfquery name="this_query" datasource="#SESSION.DataSourceWFCDB#">
            SELECT tEMERGENCY_CONTACTS.EmergencyContactIdNo, tEMERGENCY_CONTACTS.EmergencyContactCallOrder, tEMERGENCY_CONTACTS.RelationCodeIdNo, 
            tPERSONS.FirstName, tPERSONS.LastName, tPERSON_ADDRESSES.PersonAddress1, tPERSON_ADDRESSES.PersonAddress2, 
            tPERSON_ADDRESSES.PersonAddressStateProvinceIdNo, tPERSONS.MiddleName, tPERSON_ADDRESSES.PersonAddressCity, 
            tPERSON_ADDRESSES.PersonAddressPostalCode, tLOCAL_CODES.LocalCode, tLOCAL_CODES.LocalCodeAbbreviation AS State,
            tPERSON_PHONES.PersonPhoneTypeIdNo, tPERSON_PHONES.PersonPhonePrimaryInd, tPERSON_PHONES.PersonPhoneNo, 
            tPERSON_PHONES.PersonPhoneExt
            FROM tEMERGENCY_CONTACTS INNER JOIN
            tPERSON_ADDRESSES ON tEMERGENCY_CONTACTS.EmergencyContactIdNo = tPERSON_ADDRESSES.PersonIdNo INNER JOIN
            tPERSONS ON tEMERGENCY_CONTACTS.EmergencyContactIdNo = tPERSONS.PersonIdNo INNER JOIN
            tLOCAL_CODES ON tPERSON_ADDRESSES.PersonAddressStateProvinceIdNo = tLOCAL_CODES.LocalCodeIdNo  INNER JOIN
            tPERSON_PHONES ON tEMERGENCY_CONTACTS.EmergencyContactIdNo = tPERSON_PHONES.PersonIdNo
            <cfif arguments.PersonIDNo NEQ -1>
                WHERE tEMERGENCY_CONTACTS.PersonIDNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.PersonIDNo#" /> AND
                (GETDATE() BETWEEN EmergencyFromEffectDate AND EmergencyToEffectDate) AND
                tPERSON_PHONES.PersonPhonePrimaryInd = 1
            </cfif>
        </cfquery>
        <cfreturn this_query />
    </cffunction>
    
    
    <cffunction access="public" name="getEmployeeEducation" returntype="query">
        <cfargument default="-1" name="PersonIDNo" required="false" />
        <cfquery name="this_query" datasource="#SESSION.DataSourceWFCDB#">
            SELECT tEDUCATION.PersonIdNo, tEDUCATION.GradePointAverage, tEDUCATION.GraduationYear, tEDUCATION.YearsAttended, 
            tEDUCATION.DateStartedEducation, eca.EducationCodeDescription AS Degree, ecb.EducationCodeDescription AS College, ecc.EducationCodeDescription AS Major
            FROM dbo.tEDUCATION LEFT OUTER JOIN
            dbo.tEDUCATION_CODES AS ecc ON dbo.tEDUCATION.MajorCode = ecc.EducationCodeIdNo LEFT OUTER JOIN
            dbo.tEDUCATION_CODES AS eca ON dbo.tEDUCATION.DegreeCode = eca.EducationCodeIdNo LEFT OUTER JOIN
            dbo.tEDUCATION_CODES AS ecb ON dbo.tEDUCATION.SchoolCode = ecb.EducationCodeIdNo
            <cfif arguments.PersonIDNo -1>
                WHERE tEDUCATION.PersonIDNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.PersonIDNo#" /> AND
                (GETDATE() BETWEEN EducationFromEffectDate AND EducationToEffectDate)
            </cfif>
        </cfquery>
        <cfreturn this_query />
    </cffunction>
    
    
    
    <cffunction access="public" name="getEmployeePayStatus" returntype="query">
        <cfargument default="-1" name="PersonIDNo" required="false" />
        <cfquery name="this_query" datasource="#SESSION.DataSourceWFCDB#">
            SELECT CURRENT_EMPLOYEES.FirstName, CURRENT_EMPLOYEES.LastName, tPAY_STATUS.PayRate, tPAY_STATUS.PayStatusCompensationTypeIdNo, 
            tPAY_STATUS.PayRateFrequencyCodeIdNo,  tPAY_STATUS.AnnualizingFactor,  tPAY_STATUS.BasePayInd,  tPAY_STATUS.StandardUnitsPerWeek, 
            tPAY_STATUS.LastAnnualizedPayRate
            FROM CURRENT_EMPLOYEES INNER JOIN
            tPAY_STATUS ON CURRENT_EMPLOYEES.PersonIdNo =  tPAY_STATUS.PersonIdNo
            <cfif arguments.PersonIDNo -1>
                WHERE tPAY_STATUS.PersonIDNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.PersonIDNo#" /> AND
                (GETDATE() BETWEEN PayStatusFromEffectDate AND PayStatusToEffectDate)
            </cfif>
        </cfquery>
        <cfreturn this_query />
    </cffunction>

    
</cfcomponent>